package com.simplehire.repository;

import com.simplehire.entity.JobDescription;
import com.simplehire.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 岗位描述数据访问层
 */
@Repository
public interface JobDescriptionRepository extends JpaRepository<JobDescription, Long> {
    
    /**
     * 根据标题查找岗位
     */
    List<JobDescription> findByTitleContainingIgnoreCase(String title);
    
    /**
     * 根据公司名称查找岗位
     */
    List<JobDescription> findByCompanyNameContainingIgnoreCase(String companyName);
    
    /**
     * 根据行业查找岗位
     */
    List<JobDescription> findByIndustryContainingIgnoreCase(String industry);
    
    /**
     * 根据工作地点查找岗位
     */
    List<JobDescription> findByWorkLocationContainingIgnoreCase(String location);
    
    /**
     * 根据经验要求查找岗位
     */
    List<JobDescription> findByExperienceLevel(String experienceLevel);
    
    /**
     * 根据学历要求查找岗位
     */
    List<JobDescription> findByEducationLevel(String educationLevel);
    
    /**
     * 根据工作类型查找岗位
     */
    List<JobDescription> findByJobType(String jobType);
    
    /**
     * 查找激活状态的岗位
     */
    List<JobDescription> findByIsActiveTrue();
    
    /**
     * 根据创建者查找岗位
     */
    List<JobDescription> findByCreatedByAndIsActiveTrue(User createdBy);
    
    /**
     * 根据ID和激活状态查找岗位
     */
    Optional<JobDescription> findByIdAndIsActiveTrue(Long id);
    
    /**
     * 根据多个条件搜索岗位
     */
    @Query("SELECT jd FROM JobDescription jd WHERE jd.isActive = true " +
           "AND (:title IS NULL OR LOWER(jd.title) LIKE LOWER(CONCAT('%', :title, '%'))) " +
           "AND (:company IS NULL OR LOWER(jd.companyName) LIKE LOWER(CONCAT('%', :company, '%'))) " +
           "AND (:location IS NULL OR LOWER(jd.workLocation) LIKE LOWER(CONCAT('%', :location, '%'))) " +
           "AND (:industry IS NULL OR LOWER(jd.industry) LIKE LOWER(CONCAT('%', :industry, '%'))) " +
           "AND (:experienceLevel IS NULL OR jd.experienceLevel = :experienceLevel) " +
           "AND (:educationLevel IS NULL OR jd.educationLevel = :educationLevel) " +
           "AND (:jobType IS NULL OR jd.jobType = :jobType) " +
           "ORDER BY jd.createdAt DESC")
    List<JobDescription> searchJobs(@Param("title") String title,
                                   @Param("company") String company,
                                   @Param("location") String location,
                                   @Param("industry") String industry,
                                   @Param("experienceLevel") String experienceLevel,
                                   @Param("educationLevel") String educationLevel,
                                   @Param("jobType") String jobType);
    
    /**
     * 根据技能搜索岗位
     */
    @Query("SELECT DISTINCT jd FROM JobDescription jd JOIN jd.requiredSkills skill " +
           "WHERE jd.isActive = true AND LOWER(skill) LIKE LOWER(CONCAT('%', :skill, '%'))")
    List<JobDescription> findByRequiredSkillsContaining(@Param("skill") String skill);
    
    /**
     * 获取最新的岗位
     */
    @Query("SELECT jd FROM JobDescription jd WHERE jd.isActive = true ORDER BY jd.createdAt DESC")
    List<JobDescription> findLatestJobs();
    
    /**
     * 获取热门岗位（根据匹配次数）
     */
    @Query("SELECT jd FROM JobDescription jd " +
           "LEFT JOIN ResumeMatchResult rmr ON rmr.jobDescription = jd " +
           "WHERE jd.isActive = true " +
           "GROUP BY jd " +
           "ORDER BY COUNT(rmr) DESC, jd.createdAt DESC")
    List<JobDescription> findPopularJobs();
    
    /**
     * 统计激活的岗位数量
     */
    @Query("SELECT COUNT(jd) FROM JobDescription jd WHERE jd.isActive = true")
    Long countActiveJobs();
    
    /**
     * 根据创建者统计岗位数量
     */
    Long countByCreatedByAndIsActiveTrue(User createdBy);
}